package mb.admin.dao;

import java.sql.SQLException;
import java.util.ArrayList;

import javax.faces.model.SelectItem;
import javax.xml.xpath.XPathExpressionException;

import mb.admin.data.CustomersData;
import mb.commons.AbstractDAO;
import mb.commons.DbConnection;
import mb.commons.XmlHelper;

/**
 * Klasa dostępu do bazy danych dla menagera klientów
 * @author Michał Budziło
 *
 */
public class CustomersDAO extends AbstractDAO {
	
	/**
	 * Konstruktor
	 */
	public CustomersDAO() {
		conn = DbConnection.getConnection();
		helper = new XmlHelper("WEB-INF/xml/admin/customers.xml");
	}

	
	
	/**
	 * Metoda pobiera z bazy danych listę klientów
	 * @return lista klientów
	 */
	public ArrayList<CustomersData> getCustomerList() {
		ArrayList<CustomersData> list = new ArrayList<CustomersData>();
		CustomersData data;
		try {
			query = helper.getSqlString("customersList",
					"sqlString/prepareMain/sql");
			stmt = conn.createStatement();
			rs = stmt.executeQuery(query);
			while (rs.next()) {
				data = new CustomersData();
				data.setIdKlienta(rs.getInt("id_klienta"));
				data.setIdUzytk(rs.getInt("id"));
				data.setImie(rs.getString("imie"));
				data.setNazwisko(rs.getString("nazwisko"));
				data.setAdres(rs.getString("adres"));
				data.setMiasto(rs.getString("miasto"));
				data.setKod(rs.getString("kod"));


				list.add(data);
			}
			return list;
		} catch (SQLException e) {
			error(e.getErrorCode() + " : " + e.getMessage());
			e.printStackTrace();
		} catch (XPathExpressionException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
		return null;
	}
	
	
	/**
	 * Metoda wstawia do bazy danych nowego klienta
	 * @param data obiekt klasy klieta
	 */
	public void insertData(CustomersData data) {
		
				try {
					query = helper.getSqlString("insertData","sqlString/prepareMain/prepareStatment");
					prepStmt = conn.prepareStatement(query);
					prepStmt.setInt(1, data.getIdUzytk());
					prepStmt.setString(2, data.getImie());
					prepStmt.setString(3, data.getNazwisko());
					prepStmt.setString(4, data.getAdres());
					prepStmt.setString(5, data.getMiasto());
					prepStmt.setString(6, data.getKod());
					
					prepStmt.executeUpdate();
					
				} catch (SQLException e) {
					error(e.getErrorCode() + " : " + e.getMessage());
					e.printStackTrace();
				} catch (XPathExpressionException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} finally {
					
					close();
				}
				
			}

	/**
	 * Metoda aktualizuje w bazie danych wybranego klienta
	 * @param data obiekt klasy klient
	 */
	public void updateData(CustomersData data) {
		try {
			query = helper.getSqlString("updateData","sqlString/prepareMain/prepareStatment");
			prepStmt = conn.prepareStatement(query);
			prepStmt.setInt(1, data.getIdUzytk());
			prepStmt.setString(2, data.getImie());
			prepStmt.setString(3, data.getNazwisko());
			prepStmt.setString(4, data.getAdres());
			prepStmt.setString(5, data.getMiasto());
			prepStmt.setString(6, data.getKod());
			prepStmt.setInt(7, data.getIdKlienta());
			
			prepStmt.executeUpdate();
			
		} catch (SQLException e) {
			error(e.getErrorCode() + " : " + e.getMessage());
			e.printStackTrace();
		} catch (XPathExpressionException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			
			close();
		}
		
	}

	
	
	/**
	 * Metoda usuwa z bazy danych wybranego klienta
	 * @param data obiekt klasy klient
	 */
	public void  deleteData(CustomersData data) {
		
		try {
			query = helper.getSqlString("deleteData","sqlString/prepareMain/prepareStatment");
			prepStmt = conn.prepareStatement(query);
			prepStmt.setInt(1, data.getIdKlienta());
			
			prepStmt.executeUpdate();
			
		} catch (SQLException e) {
			error(e.getErrorCode() + " : " + e.getMessage());
			e.printStackTrace();
		} catch (XPathExpressionException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			
			close();
		}
	}
	
	
	/**
	 * Metoda pobiera z bazy danych listę użytkowników
	 * @return zwraca listę użytkowników
	 */
	public ArrayList<SelectItem> getUzytkownicyList() {

		ArrayList<SelectItem> list = new ArrayList<SelectItem>();
		try {
			query = helper.getSqlString("getUzytkownicyList",
					"sqlString/prepareMain/sql");
			stmt = conn.createStatement();
			rs = stmt.executeQuery(query);
			while (rs.next()) {
				SelectItem tmp = new SelectItem();
				tmp.setLabel(rs.getInt("id") + " | " + rs.getString("login"));
				tmp.setValue(rs.getInt("id"));

				list.add(tmp);
			}
			return list;
		} catch (SQLException e) {
			error(e.getErrorCode() + " : " + e.getMessage());
			e.printStackTrace();
		} catch (XPathExpressionException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
		return null;

	}
}
